﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Xml;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data;
using InteropExcel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Configuration;
using FA_Server;
using System.Data;


namespace FixedAssets
{
    public partial class _Default : System.Web.UI.Page
    {
        string sql_insert_fa_masterdata_excel = @"(scorpcode, smastercardcode, column2, column3, column4, column5, column6, column7,column8, column9,
column10, column11, column12,column13, column14, column15, column16, column17, column18,column19, 
column20, column21, column22, column23, column24, column25, column26, column27, column28,column29, 
column30, column31, column32, column33, column34,column35, column36, column37, column38, column39, 
column40,column41, column42,column43, column44,column45, column46,column47,column48, column49, 
column50, column51, column52, column53, column54, column55, column56) 
VALUES(:scorpcode, :smastercardcode, :column2, :column3, :column4, :column5, :column6, :column7,:column8, :column9,
:column10, :column11, :column12,:column13, :column14, :column15, :column16, :column17, :column18,:column19, 
:column20, :column21, :column22, :column23, :column24, :column25, :column26, :column27, :column28,:column29, 
:column30, :column31, :column32, :column33, :column34,:column35, :column36, :column37, :column38, :column39, 
:column40,:column41, :column42,:column43, :column44,:column45, :column46,:column47,:column48, :column49, 
:column50, :column51, :column52, :column53, :column54, :column55, :column56)";

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void dr_Click(object sender, EventArgs e)
        {
            if (this.FileUpload1.HasFile)
            {
                DataTable inputdt = new DataTable();

                string spath = Server.MapPath(this.FileUpload1.FileName.ToString());
                this.FileUpload1.SaveAs(spath);
                inputdt = ExcelDataSource(spath, "").Tables[0];

                Dictionary<string, int> coldic = COLUMNIndex(inputdt.Columns);

                bool b = SaveData(inputdt, coldic);
                Response.Write("<script>alert(" + b.ToString() + ")</script>");
            }
            else
            {

            }
            
        }
        public DataSet ExcelDataSource(string filepath, string sheetname)
        {
            //string strConn;
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            //OleDbConnection conn = new OleDbConnection(strConn);
            //OleDbDataAdapter oada = new OleDbDataAdapter("select * from [S1$]", strConn);
            DataSet ds = new DataSet();
            //oada.Fill(ds);

            object missing = System.Reflection.Missing.Value;
            InteropExcel.Application excel = new InteropExcel.Application();
            if (excel == null)
            {
                Response.Write("<script>alert(不能读取excel;)</script>");
            }
            else
            {
                excel.Visible = false;
                excel.UserControl = true;
                InteropExcel.Workbook wb = excel.Application.Workbooks.Open(filepath, missing, true, missing, missing, missing,
                     missing, missing, missing, missing, missing, missing, missing, missing, missing);
                InteropExcel.Worksheet ws = (InteropExcel.Worksheet)wb.Worksheets.get_Item(1);
                DataTable dt = new DataTable();
                for (int j = 1; j <= ws.Cells.CurrentRegion.Columns.Count; j++)
                {
                    InteropExcel.Range temp = (InteropExcel.Range)ws.Cells[1, j];
                    string strValue = temp.Text.ToString();
                    dt.Columns.Add(strValue);
                }
                for (int i = 2; i <= ws.Cells.CurrentRegion.Rows.Count; i++)   //把工作表导入DataTable中
                {
                    DataRow myRow = dt.NewRow();


                    for (int j = 1; j <= ws.Cells.CurrentRegion.Columns.Count; j++)
                    {
                        InteropExcel.Range temp = (InteropExcel.Range)ws.Cells[i, j];
                        string strValue = temp.Text.ToString();
                        myRow[j - 1] = strValue;
                    }
                    dt.Rows.Add(myRow);
                }
                ds.Tables.Add(dt);
                excel.Quit();
                excel = null;
                //Process[] proces = Process.GetProcessesByName("excel");
                //foreach (Process pro in proces)
                //{
                //    pro.Kill();
                //}
                //GC.Collect();

            }
            return ds;

        }



        private bool SaveData(DataTable dt, Dictionary<string, int> coldic)
        {
            DAC DAC = new FA_Server.DAC();
            DbConnection conn = DAC.CreateConnection();
            conn.Open();
            DbTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            DbCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;
            try
            {
                cmd = DAC.GetSqlStringCommand(" insert into fa_masterdata_excel" + sql_insert_fa_masterdata_excel);
                foreach (DataRow dr in dt.Rows)
                {
                    DAC.AddInParameter(cmd, "smastercardcode", DbType.String, dr[coldic["SMASTERCARDCODE"]].ToString());
                    for (int i = 2; i <= 56; i++)
                    {
                        if (coldic["COLUMN" + i] >= 0)
                        {
                            DAC.AddInParameter(cmd, "column" + i, DbType.String, dr[coldic["COLUMN" + i]].ToString());
                        }
                        else
                        {
                            DAC.AddInParameter(cmd, "column" + i, DbType.String, string.Empty);
                        }
                    }
                    DAC.AddInParameter(cmd, "scorpcode", DbType.String, "S000000001");
                    DAC.ExecuteNonQuery(cmd);
                    DAC.ClearParameterCache();
                }
                tran.Commit();
                conn.Close();
                return true;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw ex;
            }


        }

        private Dictionary<string, int> COLUMNIndex(DataColumnCollection dcol)
        {
            Dictionary<string, int> rdic = new Dictionary<string, int>();
            OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString);
            OracleCommand cmd = conn.CreateCommand();
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.CommandText = " select scode,sname from fa_masterdata_excel_property";
                DataTable dtc = new DataTable();
                using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dtc = ds.Tables[0];
                }

                for (int i = 0; i < dtc.Rows.Count; i++)
                {
                    int j = -1;
                    if (dcol.Contains(dtc.Rows[i]["sname"].ToString()))
                        j=dcol.IndexOf(dtc.Rows[i]["sname"].ToString());
                    rdic.Add(dtc.Rows[i]["scode"].ToString(), j);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return rdic;
        }


    }
}



















